package EX02_Account;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jheej5024.J03_MemberVO;

public class AccountDAO implements IAccountDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:jheej5024/jheej5024@112.216.253.125:1521:xe";
	private String insertSQL = "insert into Account values(?,?,?,?,sysdate)";
	private String selectSQL = "select * from Account order by mno desc";
	private String updateSQL = "update Account set passwd=?, name=?, email=?,regdate=sysdate where mno=?";
	private String deleteSQL = "delete Account where mno=?";
	
	
	
	
	@Override
	public boolean insertAccount(AccountVO avo) {
		
		boolean result = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, avo.getAid());
			pstmt.setString(2, avo.getBalance());
			pstmt.setString(3, avo.getInterestRate());
					
			pstmt.executeUpdate();
			result = true;
			
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn,pstmt,null);
		}
	     return result;
	}
	
	
	
	private Connection openDatebase(){
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
	
	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) 
	{
		if(r != null)try {r.close();r = null;}catch(Exception e) {}
		if(p != null)try {p.close();p = null;}catch(Exception e) {}
		if(c != null)try {c.close();c = null;}catch(Exception e) {}
		
	}
	@Override
	public List <AccountVO> selectmember() {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
		List <AccountVO> result = new ArrayList<>();
	
		try {
			conn = openDatebase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				AccountVO m = new AccountVO();
				 m.setAid(rs.getString("aid"));
				 m.setBalance(rs.getString("balance"));
				 m.setInterestRate(rs.getString("interestRate"));
				 m.setDateCreated(rs.getString("dateCreated"));
				result.add(m);
			}
	
		}catch (Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, rs);
		}
	return result;
	}
	@Override
	public boolean updateMember(AccountVO avo) {
		// TODO Auto-generated method stub
		return false;
	}
	
	
	
	
	
	
}
